﻿using IFSModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Script.Serialization;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using IFSModel;
using System.Data.Common;

namespace IFSController
{
    public class member
    {
        public static string SetAuthenUserFB(string username, string accessToken)
        {
            try
            {
                System.Web.Security.FormsAuthentication.SetAuthCookie(username, true);
                HttpContext.Current.Session["AccessToken"] = accessToken;
                HttpContext.Current.Session["curentUser"] = username;
                updateLastActive(username);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            return "true";
        }
        public static bool SetAuthenUser(string username)
        {
            try
            {
                System.Web.Security.FormsAuthentication.SetAuthCookie(username, true);
                updateLastActive(username);
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }
        private static bool updateLastActive(string username)
        {
            var dataContext = new IFNDBDataContext();
            try
            {
                var user = dataContext.Members.Single(u => u.UserName == username);
                user.LastActive = DateTime.Now;
                dataContext.SubmitChanges();
                return true;
            }
            catch (Exception)
            {

                return false;
            }

        }
        public static bool IsAuthenticate()
        {
            return System.Web.HttpContext.Current.User.Identity.IsAuthenticated;
        }
        public static string GetMember()
        {
            var currentUser = System.Web.HttpContext.Current.User.Identity.Name;
            return GetMember(currentUser);
        }
        public static string GetMember(string username)
        {
            var dataContext = new IFNDBDataContext();
            var u = dataContext.Members.Where(i => i.UserName == username);
            var serializer = new JavaScriptSerializer();
            return serializer.Serialize(u);
        }

        public static int AddFriendInvite(List<friendsInvite> data, out IFSController.Helper.Result result)
        {

            using (var dc = new IFNDBDataContext())
            {
                result = new Helper.Result(true);
                if (data.Count > 0)
                {
                    try
                    {
                        dc.friendsInvites.InsertAllOnSubmit(data);
                        dc.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        result.Msg = ex.Message;
                        result.Success = false;
                    }
                    return data.Count;
                }
                return 0;
            }
        }
        public static int UpdateFriendInvite(List<friendsInvite> data, out Helper.Result result)
        {
            using (var dc = new IFNDBDataContext())
            {
                result = new Helper.Result(true);
                if (data.Count > 0)
                {
                    try
                    {
                        //add star only new friends invite
                        foreach (var friendsInvite in data)
                        {
                            try
                            {
                                var alreadyInvite = dc.friendsInvites.Single(f => f.requestId == friendsInvite.requestId);
                                var alreadyInvitedID = from fi in dc.friendsInvites
                                                       where fi.requestTo == alreadyInvite.requestTo && fi.status == 1
                                                       select fi;

                                if (!alreadyInvitedID.Any())
                                {
                                    string resultaddstar;
                                    AddStar(alreadyInvite.requesterEmail,
                                            4, 6, true, out resultaddstar); //add 4 star and type 6 is star from friends invite
                                }
                                var fi2 = dc.friendsInvites.Single(f => f.requestId == friendsInvite.requestId);
                                fi2.status = 1;
                                dc.SubmitChanges();
                            }
                            catch (Exception ex)
                            {

                                result.Msg = ex.Message;
                                result.Success = false;
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        result.Msg = ex.Message;
                        result.Success = false;
                    }
                    return data.Count;
                }
                return 0;
            }
        }
        public static void AddStar(int star, int typeid, out string output)
        {
            output = string.Empty;
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    var cu = System.Web.HttpContext.Current.User.Identity.Name;
                    AddStar(cu, star, typeid, out output);
                }
                catch (Exception ex)
                {
                    output = ex.Message;
                }
            }
        }
        public static void AddStar(string user, int star, int typeid, out string output)
        {
            output = string.Empty;
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    output += user;
                    var users = dataContext.Members.Single(prop => prop.UserName == user);
                    var alreadShare = dataContext.Members_point_trans.Where(u => u.MemID == users.MemID && u.TypeID == typeid);
                    if (!alreadShare.Any())
                    {
                        dataContext.Members_point_trans.InsertOnSubmit(new Members_point_tran
                        {
                            MemID = users.MemID,
                            Mpt_Cal_Star = 4,
                            TypeID = typeid,
                            Createdate = DateTime.Now,
                            Active = 1
                        });
                        users.Curent_Star += star;
                        output += alreadShare.Count() + " " + users.MemID + " " + users.Curent_Star;
                        dataContext.SubmitChanges();

                    }

                }
                catch (Exception ex)
                {
                    output = ex.Message;
                }
            }
        }
        public static void AddStar(string user, int star, int typeid, bool allowDuplicate, out string output)
        {
            output = string.Empty;
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    output += user;
                    var users = dataContext.Members.Single(prop => prop.UserName == user);
                    if (!allowDuplicate)
                    {
                        var alreadShare = dataContext.Members_point_trans.Where(u => u.MemID == users.MemID && u.TypeID == typeid);
                        if (!alreadShare.Any())
                        {
                            dataContext.Members_point_trans.InsertOnSubmit(new Members_point_tran
                            {
                                MemID = users.MemID,
                                Mpt_Cal_Star = 4,
                                TypeID = typeid,
                                Createdate = DateTime.Now,
                                Active = 1
                            });
                            users.Curent_Star += star;
                            output += alreadShare.Count() + " " + users.MemID + " " + users.Curent_Star;
                            dataContext.SubmitChanges();

                        }
                    }
                    else
                    {
                        dataContext.Members_point_trans.InsertOnSubmit(new Members_point_tran
                        {
                            MemID = users.MemID,
                            Mpt_Cal_Star = 4,
                            TypeID = typeid,
                            Createdate = DateTime.Now,
                            Active = 1
                        });
                        users.Curent_Star += star;
                        users.Updatedate = DateTime.Now;
                        output += users.MemID + " " + users.Curent_Star;
                        dataContext.SubmitChanges();
                    }

                }
                catch (Exception ex)
                {
                    output = ex.Message;
                }
            }
        }
        public static int AutoAddStar()
        {
            const int addStar = 10;
            using (var dataContext = new IFNDBDataContext())
            {
                try
                {
                    var users = from user in dataContext.Members
                                where user.Curent_Star < 10
                                select user;
                    List<Members_point_tran> userTrans = new List<Members_point_tran>();
                    List<MemberNotification> memberNoti = new List<IFSModel.MemberNotification>();
                    var userLastActive = from user in dataContext.Members
                                         join alreadyAdd in userTrans on user.MemID equals alreadyAdd.MemID
                                         where (DateTime.Now - user.LastActive.Value).Hours < 24 && (DateTime.Now - alreadyAdd.Createdate.Value).Hours >= 23
                                         select user;
                    foreach (var u in userLastActive)
                    {
                        var rand = new Random();
                        int lottory = rand.Next(1, 20);
                        userTrans.Add(new Members_point_tran
                        {
                            MemID = u.MemID,
                            TypeID = 4, //Type 4 is the lottory
                            Createdate = DateTime.Now,
                            Mpt_Cal_Star = lottory,
                            Mpt_Cal_Point = 0,
                            Active = 1
                        });
                        var member =
                                           dataContext.Members.Single(prop => prop.MemID == u.MemID);
                        if (member != null)
                        {
                            member.Curent_Star += lottory;
                            member.Updatedate = DateTime.Now;
                        }
                        memberNoti.Add(new IFSModel.MemberNotification { message = "คุณได้รับ Lottery ประจำวัน " + lottory + " ดาว", UserName = u.UserName, star = lottory, notiTypeId = 1, isRead = false, isDisable = false, point = 0 });
                    }

                    foreach (var m in users)
                    {
                        var member =
                                       dataContext.Members.Single(prop => prop.MemID == m.MemID);
                        if (member != null)
                        {
                            member.Curent_Star = addStar;
                            member.Updatedate = DateTime.Now;
                        }
                        userTrans.Add(new Members_point_tran
                        {
                            MemID = member.MemID,
                            TypeID = 2,
                            Createdate = DateTime.Now,
                            Mpt_Cal_Star = addStar - member.Curent_Star,
                            Mpt_Cal_Point = 0,
                            Active = 1
                        });
                    }
                    dataContext.MemberNotifications.InsertAllOnSubmit(memberNoti);
                    dataContext.Members_point_trans.InsertAllOnSubmit(userTrans);
                    dataContext.SubmitChanges();
                    return users.Count();
                }
                catch (Exception ex)
                {
                    Helper.AddLog(new log
                    {
                        details =
                            ex.Message,
                        username = "Add Star",
                        logType = 1
                    });
                    return -1;
                }
            }
        }
        public static List<MemberNotification> getNotify()
        {
            var username = System.Web.HttpContext.Current.User.Identity.Name;
            var dataContext = new IFNDBDataContext();
            var noti = dataContext.MemberNotifications.Where(i => i.UserName == username && i.isRead != true);
            return noti.ToList();
        }
        public static string GetMemberRanking(string orderType, string leagueid)
        {
            using (var dataContext = new IFNDBDataContext()){
                dataContext.CommandTimeout = 180;
                
                
                var palyAll = from upm in dataContext.Members_point_trans
                              join up in dataContext.Members_plays on upm.RefID equals up.MempID
                              where upm.TypeID == 3
                              select new { up.MemID, 
                                  up.LeagueID, 
                                  up.Createdate,
                                  upm.Mpt_Cal_Point
                              };
               
                if (!string.IsNullOrEmpty(leagueid))
                {
                    palyAll = palyAll.Where(up => up.LeagueID == leagueid);
                } 
                var uplayall = from upm in palyAll
                                     group upm.Mpt_Cal_Point by upm.MemID into g2
                                     select new
                                     {
                                         mid = g2.Key,
                                         Score = g2.Sum(),
                                         count = g2.Count()
                                     };


               
                var uplaythisMonth = from upm in palyAll
                                     where upm.Createdate.Value.Month == DateTime.Today.Month
                                     group upm.Mpt_Cal_Point by upm.MemID into g2
                                     select new
                                     {
                                         mid= g2.Key,
                                         ScoreMonth = g2.Sum()
                                         
                                     };
                
                 var result  = (from u in dataContext.Members
                                
                              join upall2 in uplayall.DefaultIfEmpty() on u.MemID equals upall2.mid
                                
                               orderby u.Curent_Point descending, u.Curent_Star descending,
                                   u.Createdate
                               select new 
                                          {
                                              u.MemID,
                                              u.FirstName,
                                              u.LastName,
                                              upall2.Score,
                                              //upm.ScoreMonth,
                                               upall2.count,
                                              u.Curent_Star,
                                              u.Createdate
                                          });

                
                //DbCommand dc = dataContext.GetCommand(k2);
                //var c = dc.CommandText;
                var userRank = new List<MemberRank>();
                switch (orderType)
                {
                    case "sumscore":
                        userRank = (from u in result
                                    orderby u.Score descending , u.Curent_Star descending , u.count ,
                                        u.Createdate
                                    select new MemberRank
                                               {
                                                   count = u.count,
                                                   FirstName = u.FirstName,
                                                   LastName = u.LastName,
                                                   Createdate = u.Createdate,
                                                   Curent_Star = u.Curent_Star,
                                                   MemID = u.MemID,
                                                   Score = u.Score
                                               }).ToList();
                        break;
                    //case "monthscore":
                        //userRank = result.OrderBy(u => u.ScoreMonth).OrderByDescending(u2 => u2.curent_Star);
                        //userRank = (from u in userRank
                        //           orderby u.ScoreMonth descending, u.Curent_Star descending, u.count,
                        //               u.Createdate
                        //           select new MemberRank
                        //                       {
                        //                           count = u.count,
                        //                           FirstName = u.FirstName,
                        //                           LastName = u.LastName,
                        //                           Createdate = u.Createdate,
                        //                           Curent_Star = u.Curent_Star,
                        //                           MemID = u.MemID,
                        //                           Score = u.Score
                        //                       }).ToList();
                            //from u in userRank
                            //       orderby u.ScoreMonth descending, u.Curent_Star descending, u.count,
                            //           u.Createdate
                            //       select u;
                       // break;
                    case "sumstar":
                        userRank = (from u in result
                                   orderby u.Curent_Star descending, u.Score descending, u.count,
                                       u.Createdate
                                   select new MemberRank
                                               {
                                                   count = u.count,
                                                   FirstName = u.FirstName,
                                                   LastName = u.LastName,
                                                   Createdate = u.Createdate,
                                                   Curent_Star = u.Curent_Star,
                                                   MemID = u.MemID,
                                                   Score = u.Score
                                               }).ToList();
                        break;
                    default:
                        userRank = (from u in result
                                    select new MemberRank
                                    {
                                        count = u.count,
                                        FirstName = u.FirstName,
                                        LastName = u.LastName,
                                        Createdate = u.Createdate,
                                        Curent_Star = u.Curent_Star,
                                        MemID = u.MemID,
                                        Score = u.Score
                                    }).ToList();
                        break;
                }

                    foreach (var u in userRank)
                    {
                        foreach (var item in uplaythisMonth)
                        {
                            if (u.MemID == item.mid)
                            {
                                u.ScoreMonth = item.ScoreMonth;
                            }
                        }
                    }
                    var serializer = new JavaScriptSerializer();
                    var r = userRank.Select(k => new { k.FirstName, k.LastName, k.Curent_Star, k.Score, k.ScoreMonth });
                    if (orderType == "monthscore")
                    {
                        r = r.OrderByDescending(u => u.ScoreMonth);
                    }
                    return serializer.Serialize(r);
                
                
            }
        }

        public static bool UpdateNoti(string notiID)
        {
            try
            {
                if (string.IsNullOrEmpty(notiID))
                {
                    return false;
                }
                var username = System.Web.HttpContext.Current.User.Identity.Name;
                var dataContext = new IFNDBDataContext();
                var noti = dataContext.MemberNotifications.Single(i => i.id == Convert.ToInt32(notiID));
                noti.isRead = true;
                dataContext.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {

                return false;
            }

        }

        public static bool login(string email, string password)
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = "select 1 from members where email=@email and password=@password";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            if (!string.IsNullOrEmpty(email))
                adapter.SelectCommand.Parameters.AddWithValue("@email", email);
            if (!string.IsNullOrEmpty(password))
                adapter.SelectCommand.Parameters.AddWithValue("@password", password);


            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                Conn.Close();
                return false;
            }
            finally
            {
                Conn.Close();
            }

            return true;

        }

        public static DataTable chkconfirmregister(string memid)
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();


            string query = "select  * from members where  memid = @memid and active = @active";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            if (!string.IsNullOrEmpty(memid))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@memid", memid);
                adapter.SelectCommand.Parameters.AddWithValue("@active", 0);
            }

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                return dt = null;
            }

            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable sendpassword(string param)
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = "select  email,password from members where  email = @param and active = 1";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            if (!string.IsNullOrEmpty(param))
                adapter.SelectCommand.Parameters.AddWithValue("@param", param);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable chkdubplicate(string method, string param)
        {
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = "select " + method + "  from members where " + method + " = @param ";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            if (!string.IsNullOrEmpty(param))
                adapter.SelectCommand.Parameters.AddWithValue("@param", param);


            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable getprofile(string email)
        {

            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = "select * from members where email=@email ";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            if (!string.IsNullOrEmpty(email))
                adapter.SelectCommand.Parameters.AddWithValue("@email", email);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable getsumaryall(int getall = 0)
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string username = System.Web.HttpContext.Current.User.Identity.Name.ToString();
            if (string.IsNullOrEmpty(username))
            {
                username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
            }
            DataTable dtmid = member.getprofile(username);
            long Memid = Convert.ToInt64(dtmid.Rows[0]["memid"]);


            //            string query = @"select sum(curent_point) curent_point,(select sum(Mpt_Cal_Point) Mpt_Cal_Point from Members_point_tran where memid = @MemID 
            //                                 and datepart(month,Createdate) = datepart(month,getdate()) group by memid) Mpt_Cal_Point , 0 rollwin
            //	                             from  Members where  memid = @MemID";

            //    select memid,curent_point,curent_star,
            //       RANK() OVER (ORDER BY curent_point DESC) AS xRank, 
            //       DENSE_RANK() OVER (ORDER BY curent_point DESC) AS xRank 
            //FROM 
            //(
            //select memid,curent_point,curent_star
            //from members) as a 
            //ORDER BY 1

            //      string query = @"select SUM(a.mpt_cal_point) curent_point,  
            //	                        (select SUM(a.mpt_cal_point) from Members_point_tran a where 
            //	                           datepart(month,a.Createdate) = datepart(month,getdate()) 
            //		                         and a.typeid = 1 and  a.memid = @MemID) pointperthismonth 
            //                        from Members_point_tran a where a.typeid = 3 and  a.memid = @MemID";

            //            string query = @"SELECT memid, curent_point,curent_star, RANK() OVER (ORDER BY curent_point DESC) AS RankTotal,
            //		                    (select arank = RANK() OVER (ORDER BY SUM(Mpt_Cal_Point) DESC) from Members_point_tran
            //		                     where MemID = @MemID and typeid = 3 and datepart(month,createdate) = datepart(month,getdate()) group by memid) RankThismonth
            //                    FROM (select memid,curent_point,curent_star from members) as a  where MemID = @MemID";


            string query = @"SELECT memid, FirstName,curent_point,curent_star, RANK() OVER (ORDER BY curent_point DESC) AS RankTotal ,1 sorting
                              FROM (select memid,curent_point,curent_star,FirstName from members ) as a 
                                union all 
                             select memid,'',SUM(Mpt_Cal_Point),0,arank = RANK() OVER (ORDER BY SUM(Mpt_Cal_Point) DESC), 2 sorting from Members_point_tran
                             where  typeid = 3 and datepart(month,createdate) = datepart(month,getdate()) group by memid order by sorting";


            if (getall == 1)
            {
                query = "";

                query = @"SELECT memid,FirstName, curent_point,curent_star, RANK() OVER (ORDER BY curent_point DESC) AS RankTotal ,1 sorting
                              FROM (select memid,curent_point,curent_star,FirstName from members ) as a ";

                //   union all 
                //select memid,'',SUM(Mpt_Cal_Point),0,arank = RANK() OVER (ORDER BY SUM(Mpt_Cal_Point) DESC), 2 sorting from Members_point_tran
                //where  typeid = 3 and datepart(month,createdate) = datepart(month,getdate()) group by memid order by memid,sorting";

            }


            adapter.SelectCommand = new SqlCommand(query, Conn);

            //adapter.SelectCommand.Parameters.AddWithValue("@MemID", Memid);  

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);

                if (getall == 0)
                {
                    DataTable dt2 = dt.Select("memid = " + Memid).CopyToDataTable();
                    dt = dt2;
                }

                dt.DefaultView.Sort = "sorting ASC";
            }
            catch
            {
                Conn.Close();
                dt = null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable gettop10latest()
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            DataTable dtmid = member.getprofile(System.Web.HttpContext.Current.User.Identity.Name.ToString());
            long Memid = Convert.ToInt64(dtmid.Rows[0]["memid"]);

            string query = @"select top 10 mpt_cal_point rollwin from Members_point_tran a inner join Members_play b on a.refid = b.mempid 
                             where b.memid = @MemID and a.typeid = 3 and b.isdone = 1 order by mptid desc";

            //            string query = @"select mpt.mpt_cal_point,mp.leagueid from  Members_play mp left join Members_point_tran mpt
            //	                        on mp.mempid = mpt.refid where mp.memid = @MemID and mpt.typeid = 1 and mp.isdone = 1 order by mp.leagueid,mpt.mptid desc";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            adapter.SelectCommand.Parameters.AddWithValue("@MemID", Memid);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);


            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static string getMemberPlayHistory(string leagueID, string dateFrom, string dateTo,
            int page
            )
        {
            const int historyPerPage = 10;
            using (var dc = new IFNDBDataContext())
            {

                var username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
                var playhistory = (from mp in dc.Members_plays
                                  join m in dc.Members on mp.MemID equals  m.MemID
                                  where m.UserName == username && mp.IsDone
                                  join mpt in dc.Members_point_trans on mp.MempID equals mpt.RefID
                                  where mpt.TypeID == 3
                                  join l  in dc.leagues on mp.LeagueID equals l.name
                                  join mat in dc.matches on mp.MatchID equals mat.id
                                  select new
                                             {
                                                 mp.Createdate,
                                                 l.descriptionTh,
                                                 mat.liveA,
                                                 mp.Current_PriceHandicap,
                                                 mp.Current_TeamHandicap,
                                                 mat.liveB,
                                                 mp.Selectteam,
                                                 l.type,
                                                 mp.LeagueID,
                                                 mp.StarBet,
                                                 mp.ScoreA,
                                                 mp.ScoreB,
                                                 mpt.Mpt_Cal_Point
                                             }).Distinct();
                playhistory = playhistory.OrderByDescending(p => p.Createdate);
                if (!string.IsNullOrEmpty(leagueID))
                {
                    playhistory = playhistory.Where(ph => ph.LeagueID.Trim().ToLower() == leagueID.Trim().ToLower());
                }
                if (!string.IsNullOrEmpty(dateFrom) && !string.IsNullOrEmpty(dateTo))
                {
                    playhistory = playhistory.Where(ph => ph.Createdate.Value >=  Convert.ToDateTime(dateFrom)
                        && ph.Createdate.Value <= Convert.ToDateTime(dateTo));
                }
                var serializer = new JavaScriptSerializer();
               // var count = playhistory.Count();
                //if (page > 0)
                //{
                //    playhistory = playhistory.Skip(historyPerPage * page - 1).Take(page * historyPerPage);
                //}
                var pagedQuery = from e in playhistory.Skip(historyPerPage * (page - 1)).Take(historyPerPage)
                    select new
                    {
                        Count = playhistory.Count(),
                        Entity = e
                    };

                return serializer.Serialize(pagedQuery);
            }
        }

        public static string getMemberSumaryPerLeague()
        {
            using (var dataContext = new IFNDBDataContext())
            {
                List<IFSModel.MemberLeagueSummary> mls = new List<IFSModel.MemberLeagueSummary>();
                var username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
                var memberPlayLeague = from mp in dataContext.Members_plays
                                       join m in dataContext.Members on mp.MemID equals m.MemID
                                       join l in dataContext.leagues on mp.LeagueID equals l.name
                                       where mp.IsDone && m.UserName == username && l.type == 1
                                       orderby l.priority
                                       group mp by mp.LeagueID
                                           into g
                                           select new
                                                      {
                                                          g.Key,
                                                      };



                foreach (var mpp in memberPlayLeague)
                {
                    var ml = new IFSModel.MemberLeagueSummary();
                    ml.LeagueID = mpp.Key;
                    ml.LeagueName = dataContext.leagues.Where(l => l.name == mpp.Key).Select(name => name.descriptionTh).First();
                    var allmemberPlayPerLeague = from mp in dataContext.Members_plays
                                                 join mt in dataContext.Members_point_trans on mp.MempID equals mt.RefID
                                                 join m in dataContext.Members on mp.MemID equals m.MemID
                                                 where mp.LeagueID == mpp.Key && mp.IsDone && mt.TypeID == 3
                                                 group mt by m.UserName
                                                     into g
                                                     select new
                                                                {
                                                                    Uid = g.Key,
                                                                    score = g.Sum(id => id.Mpt_Cal_Point)
                                                                };
                    var scoreMonth = from mp in dataContext.Members_plays
                                     join mt in dataContext.Members_point_trans on mp.MempID equals mt.RefID
                                     join m in dataContext.Members on mp.MemID equals m.MemID
                                     where mp.Createdate.Value.Month == DateTime.Today.Month && m.UserName == username && mp.LeagueID == mpp.Key && mp.IsDone && mt.TypeID == 3
                                     select mt.Mpt_Cal_Point;

                    ml.MemberLeagueScoreMonth = scoreMonth.Count() < 1 ? 0 : Convert.ToInt32(scoreMonth.Sum(s => s.Value));
                    ml.MemberLeagueRank = allmemberPlayPerLeague.OrderByDescending(id => id.score).ToList().Select(k => k.Uid).ToList().IndexOf(username) + 1;  //its start from 0
                    ml.MemberLeagueScore = Convert.ToInt32(allmemberPlayPerLeague.Where(u => u.Uid == username).Select(s => s.score).First());

                    var last10 = from mp in dataContext.Members_plays
                                 join mt in dataContext.Members_point_trans on mp.MempID equals mt.RefID
                                 join m in dataContext.Members on mp.MemID equals m.MemID
                                 orderby mp.Createdate descending
                                 where mp.LeagueID == mpp.Key && mp.IsDone && m.UserName == username && mt.TypeID == 3
                                
                                 select mt.Mpt_Cal_Point;
                    ml.Last10Result = last10.ToList();
                    mls.Add(ml);
                }
                var serializer = new JavaScriptSerializer();
                return serializer.Serialize(mls);
            }
        }
        public static DataTable getsumaryallperleague()
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();
            string username = System.Web.HttpContext.Current.User.Identity.Name.ToString();
            if (string.IsNullOrEmpty(username))
            {
                username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
            }
            DataTable dtmid = member.getprofile(username);
            long Memid = Convert.ToInt64(dtmid.Rows[0]["memid"]);

            //            string query = @"select a.leagueid,sum(b.mpt_cal_point) pointSeason ,
            //                        (select sum(p.Mpt_Cal_Point) Mpt_Cal_Point from Members_point_tran p left join Members_play mp on p.refid = mp.mempid
            //                          where p.memid = @MemID and mp.leagueid = a.leagueid 
            //		                    and datepart(month,p.Createdate) = datepart(month,getdate())  and mp.isdone = 1  group by mp.leagueid) pointperthismonth
            //	                    from Members_play a 
            //	                     left join Members_point_tran b on a.mempid = b.refid  
            //	                     left join members c on c.memid = a.memid
            //                         left join Members_play mp2 on a.mempid = b.refid
            //	                     where a.memid = @MemID and mp2.isdone = 1 group by a.leagueid,mpt_cal_point,Curent_Point";

            //            string query = @"select b.leagueid,sum(a.mpt_cal_point) pointSeason,(select  l.descriptionth from league l where l.name = b.leagueid) leaguename
            //                            ,(select sum(p.Mpt_Cal_Point) Mpt_Cal_Point from Members_point_tran p left join Members_play mp on p.memid = mp.memid
            //                            where p.memid = @MemID and p.typeid = 1 and mp.leagueid = b.leagueid 
            //                            and datepart(month,p.Createdate) = datepart(month,getdate()) and mp.isdone = 1 group by p.memid ) pointperthismonth 
            //                            from  Members_point_tran a 
            //                            left join members c on c.memid = a.memid
            //                            left join Members_play b on c.memid = b.memid
            //                            where a.memid = @MemID and a.typeid = 1 and b.isdone = 1 group by a.memid,b.leagueid";


            string query = @"select b.leagueid,e.priority,SUM(a.mpt_cal_point) pointSeason,(select  l.descriptionth from league l where l.name = b.leagueid) leaguename
                                ,(select SUM(p.Mpt_Cal_Point) Mpt_Cal_Point from Members_point_tran p inner join Members_play mp on mp.mempid = p.refid
                                where p.memid = @MemID and p.typeid = 3 and mp.leagueid = b.leagueid 
                                and datepart(month,p.Createdate) = datepart(month,getdate()) and mp.isdone = 1 group by p.memid ) pointperthismonth 
                                from  Members_point_tran a 
                                inner join members c on c.memid = a.memid
                                inner join Members_play b on b.mempid = a.refid
                                inner join league e on e.name = b.leagueid
                                where a.memid = @MemID and a.typeid = 3 and b.isdone = 1 
                                group by a.memid,e.priority,b.leagueid 
                                order by e.priority,b.leagueid,a.memid";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            adapter.SelectCommand.Parameters.AddWithValue("@MemID", Memid);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable gettop10latestperleague()
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();
            string username = System.Web.HttpContext.Current.User.Identity.Name.ToString();
            if (string.IsNullOrEmpty(username))
            {
                username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
            }
            DataTable dtmid = member.getprofile(username);
            long Memid = Convert.ToInt64(dtmid.Rows[0]["memid"]);

            //            string query = @"select mpt.mpt_cal_point,mp.leagueid from  Members_play mp inner join Members_point_tran mpt
            //	                        on mp.mempid = mpt.refid where mp.memid = @MemID and mpt.typeid = 3 and mp.isdone = 1 order by mp.leagueid,mpt.mptid desc";

            string query = @"select distinct mp.MatchID,mp.leagueid,mpt.mpt_cal_point,mp.createdate from  Members_play mp inner join Members_point_tran mpt
	                        on mp.mempid = mpt.refid where mp.memid = @MemID and mpt.typeid = 3 and mp.isdone = 1 order by mp.createdate desc,mp.leagueid desc";


            adapter.SelectCommand = new SqlCommand(query, Conn);

            adapter.SelectCommand.Parameters.AddWithValue("@MemID", Memid);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable getRanksumaryallperleagueRank()
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();
            string username = System.Web.HttpContext.Current.User.Identity.Name.ToString();
            if (string.IsNullOrEmpty(username))
            {
                username = System.Web.HttpContext.Current.Session["curentUser"].ToString();
            }
            DataTable dtmid = member.getprofile(username);
            long Memid = Convert.ToInt64(dtmid.Rows[0]["memid"]);

            string query = @"select b.memid,b.leagueid,
		                        SUM(a.mpt_cal_point) point,
		                        RANK() OVER (PARTITION BY b.leagueid ORDER BY SUM(a.Mpt_Cal_Point) DESC) AS RankingByLeague
		                        from  Members_point_tran a 
		                        inner join Members_play b on b.mempid = a.refid
                        where   a.typeid = 3 and b.isdone = 1   
                        group by b.leagueid,b.memid order by b.leagueid,b.memid";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            //adapter.SelectCommand.Parameters.AddWithValue("@MemID", Memid);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);

                DataTable dt2 = dt.Select("memid = " + Memid).CopyToDataTable();
                dt = dt2;
                dt.DefaultView.Sort = "memid ASC";
            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }


        public static DataTable getRankall()
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = @"SELECT memid,FirstName, curent_point,curent_star, RANK() OVER (ORDER BY curent_point DESC) AS RankpointTotal,
                            RANK() OVER (ORDER BY curent_star DESC) AS RankstarTotal
                            FROM (select memid,curent_point,curent_star,FirstName from members ) as a order by RankpointTotal,RankstarTotal";

            adapter.SelectCommand = new SqlCommand(query, Conn);


            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public static DataTable getRankallThisMonth()
        {
            connection con = new connection();
            SqlConnection Conn = new SqlConnection(connection.getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = @"select a.memid,b.FirstName,SUM(a.Mpt_Cal_Point) point, rankthismonth = RANK() OVER (ORDER BY SUM(a.Mpt_Cal_Point) DESC)  
                        from Members_point_tran a inner join members b on a.memid = b.memid
                        where  a.typeid = 3 and datepart(month,a.createdate) = datepart(month,getdate()) group by a.memid,b.FirstName";

            adapter.SelectCommand = new SqlCommand(query, Conn);


            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch
            {
                Conn.Close();
                return null;
            }
            finally
            {
                Conn.Close();
            }

            return dt;

        }
    }


}
